package com.liqi.common.utils.poi;


import org.apache.commons.io.IOUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class POIExcelUtil {

    /**
     * 导出excel
     *
     * @param response
     * @param request
     * @param fileName   文件名（不要扩展名）
     * @param headers    表头集合
     * @param fieldNames 表头对应的实体属性集合
     * @param dataList   数据
     * @param isAddTime  文件名是否增加时间后缀
     * @throws IOException
     */
    public static void exportExcel(HttpServletResponse response, HttpServletRequest request, String fileName, String[] headers, String[] fieldNames, List dataList, boolean isAddTime) throws IOException {
        String sheetName = "Sheet1";
        boolean flag = false;
        if (isAddTime) {
            fileName = fileName + getTime();
        }
        POIExcelUtil.exportExcel(response, request, fileName, sheetName, headers, fieldNames, dataList, flag);
    }

    /**
     * 导出excel
     *
     * @param response
     * @param request
     * @param fileName   文件名（不要扩展名）
     * @param sheetName  sheet页名
     * @param headers    表头集合
     * @param fieldNames 表头对应的实体属性集合
     * @param dataList   数据
     * @param flag       第一列是否为序号列
     * @throws IOException
     */
    public static void exportExcel(HttpServletResponse response, HttpServletRequest request, String fileName, String sheetName, String[] headers, String[] fieldNames, List dataList, boolean flag) throws IOException {
        fileName = fileName + ".xlsx";
        OutputStream out = null;
        try {
            out = response.getOutputStream();
            XSSFWorkbook work = new XSSFWorkbook();
            //创建工作表
            Sheet sheet = work.createSheet(sheetName);
            //显示标题
            Row title_row = sheet.createRow(0);
            title_row.setHeight((short) (40 * 20));
            Row header_row = sheet.createRow(0);
            //创建单元格的 显示样式
            CellStyle style = work.createCellStyle();
            int headcell_index = 0;
            if (flag) {//有序号
                sheet.setColumnWidth(headcell_index, 1600);
                Cell headcell = header_row.createCell(headcell_index);
                //应用样式到  单元格上
                headcell.setCellStyle(style);
                headcell.setCellValue("序号");
                headcell_index = 1;
            }
            for (int i = 0; i < headers.length; i++) {
                sheet.setColumnWidth(headcell_index, 5000);
                Cell cell = header_row.createCell(headcell_index);
                cell.setCellStyle(style);
                cell.setCellValue(headers[i]);
                headcell_index++;
            }
            //插入需导出的数据
            for (int i = 0; i < dataList.size(); i++) {
                Row row = sheet.createRow(i + 1);
                row.setHeight((short) (20 * 20)); //设置行高  基数为20
                Object obj = dataList.get(i);
                Class classType = obj.getClass();
                int cell_index = 0;
                if (flag) {//有序号
                    Cell cell = row.createCell(cell_index);
                    cell.setCellValue(i + 1);
                    cell_index = 1;
                }
                for (int j = 0; j < fieldNames.length; j++) {
                    Cell cell = row.createCell(cell_index);
                    String fieldName = fieldNames[j];
                    String firstLetter = fieldName.substring(0, 1).toUpperCase();
                    String getMethodName = "get" + firstLetter + fieldName.substring(1);
                    Method getMethod = classType.getMethod(getMethodName, new Class[]{});
                    Object value = getMethod.invoke(obj, new Object[]{});
                    if (value != null) {
                        if (value instanceof Date) {//日期类型
                            SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");//设置日期格式
                            cell.setCellValue(df.format(value));
                        } else {
                            cell.setCellValue(value.toString());
                        }
                    }
                    cell_index++;
                }
            }
            String agent = request.getHeader("USER-AGENT").toLowerCase();
            //根据浏览器类型处理文件名称
            if (agent.indexOf("msie") > -1) {
                fileName = URLEncoder.encode(fileName, "UTF-8");
            } else {  //firefox/safari不转码
                fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");
            }
            response.setContentType("application/msexcel");
            response.setHeader("Content-disposition", "attachment;filename=\"" + fileName + "\"");
            work.write(out);
            out.flush();
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
			IOUtils.closeQuietly(out);
		}
    }

    // 获取当前时间字符串
    private static String getTime() {
        return LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH_mm_ss"));
    }

    /**
     * 导入excel
     *
     * @param file       导入的文件
     * @param fieldNames 表头对应的实体属性集合
     * @param clazz      实体的class
     * @param <T>        返回的类型
     * @return
     * @throws Exception
     */
    public static <T> List<T> importExcel(MultipartFile file, String[] fieldNames, Class<T> clazz) throws Exception {
        DateFormat fmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        InputStream inputStream = file.getInputStream();
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        if (inputStream != null) {
			inputStream.close();
		}
        XSSFSheet sheet = workbook.getSheetAt(0);//获取第一个标签页
        int rows = sheet.getPhysicalNumberOfRows();
        List<T> list = new ArrayList<>(rows);
        for (int i = 1; i < rows; i++) {// 第一行是标题，从第二行开始
            XSSFRow row = sheet.getRow(i);
            if (row != null) {
                T obj = clazz.newInstance();
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    XSSFCell cell = row.getCell(j);
                    if (cell != null) {
                        Field field = obj.getClass().getDeclaredField(fieldNames[j]);
                        field.setAccessible(true);
                        String cellValue = POIExcelUtil.getCellValue(cell);
                        Class<?> type = field.getType();
                        if (type == String.class) {
                            field.set(obj, cellValue);
                        } else if (type == Integer.class) {
                            field.set(obj, Integer.valueOf(cellValue));
                        } else if (type == Long.class) {
                            field.set(obj, Long.valueOf(cellValue));
                        } else if (type == BigDecimal.class) {
                            field.set(obj, new BigDecimal(cellValue));
                        } else if (type == Date.class) {
                            field.set(obj, fmt.parse(cellValue));
                        } else if (type == Double.class) {
                            field.set(obj, Double.valueOf(cellValue));
                        } else {
                            System.err.println("无法处理的类型");
                        }
                    }
                }
                list.add(obj);
            }
        }
        return list;
    }

    // 获取单元格的值
    private static String getCellValue(XSSFCell cell) {
        DateFormat fmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        DecimalFormat df = new DecimalFormat("0.0000");
        CellType cellTypeEnum = cell.getCellTypeEnum();
        String val = "";
        switch (cellTypeEnum) {
            case _NONE: // null
                val = null;
                break;
            case BLANK: // 空白
                val = "";
                break;
            case ERROR: // 错误
                val = "";
                break;
            case STRING: // 字符型
                val = cell.getStringCellValue();
                break;
            case BOOLEAN: // 布尔
                val = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA: // 公式
                try {
                    val = String.valueOf(cell.getStringCellValue());
                } catch (IllegalStateException e) {
                    val = String.valueOf(cell.getNumericCellValue());
                }
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    val = fmt.format(cell.getDateCellValue()); //日期型
                } else {
                    val = df.format(cell.getNumericCellValue()); //数字型
                    // 去掉多余的0，如最后一位是.则去掉
                    val = val.replaceAll("0+?$", "").replaceAll("[.]$", "");
                }
                break;
            default:
                val = cell.getRichStringCellValue() == null ? null : cell.getRichStringCellValue().toString();
        }
        return val;
    }

}
